﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Siren.MFE.Bll.Constants;
using Siren.MFE.Bll.Exception;
using System.IO;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data.Common;
using System.Data;
using Siren.MFE.Bll.SirenField;

namespace Siren.MFE.Bll.Import
{
    public class ImportMgr : Core.EntityMgr
    {
        #region Import File Column Constants
       
        private const int COLUMN_ID = 0;
        private const int COLUMN_PARENT_ID = 1;
        private const int COLUMN_CAPTION = 2;
        private const int COLUMN_QUERY_TEXT = 3;
        
        private const char COLUMN_SEPERATOR = '|';

        private const String FROM = "FROM";
        private const String WHERE = "WHERE";

        #endregion Import File Column Constants

        #region Stored Procedure List

        // Stored procedure Insert imported file to database.
        private const String SP_INSERT_IMPORT_FILE = "SP_LU_SirenFieldDefinitions_InsertData";

        #endregion Stored Procedure List

        #region Import SP Input Parameters

        private const String ID                 = "@ID";
        private const String PARENT_ID          = "@ParentID";
        private const String CAPTION            = "@Caption";
        private const String QUERY_TEXT         = "@QueryText";
        private const String TABLE_NAME         = "@TableName";
        private const String ROW_IDENTIFIER     = "@RowIdentifier";

        #endregion Import SP Input Parameters

        /// <summary>
        /// Constructor
        /// </summary>
        /// <param name="ConnectionString"></param>
        public ImportMgr(String ConnectionString) : base(ConnectionString)
        { 

        }

        /// <summary>
        /// Imports the records to Database.
        /// </summary>
        /// <param name="lstImportFile"></param>
        /// <returns>Return true if all records inserted successfully</returns>
        public bool Import(List<SirenFieldInfo> lstImportFile)
        {
            try
            {
                SqlDatabase objDatabase= new SqlDatabase(this.ConnectionString);

                foreach (SirenFieldInfo importRecord in lstImportFile)
                {
                    DbCommand dbCmd = objDatabase.GetSqlStringCommand(SP_INSERT_IMPORT_FILE);

                    dbCmd.CommandType = System.Data.CommandType.StoredProcedure;

                    objDatabase.AddInParameter(dbCmd, ID,             DbType.Int32,   importRecord.ID);
                    objDatabase.AddInParameter(dbCmd, PARENT_ID,      DbType.Int32,   importRecord.ParentID);
                    objDatabase.AddInParameter(dbCmd, CAPTION,        DbType.String,  importRecord.Caption);
                    objDatabase.AddInParameter(dbCmd, QUERY_TEXT,     DbType.String,  importRecord.QueryText);
                    objDatabase.AddInParameter(dbCmd, TABLE_NAME,     DbType.String,  importRecord.TableName);
                    objDatabase.AddInParameter(dbCmd, ROW_IDENTIFIER, DbType.String,  importRecord.RowIdentifier);

                    dbCmd.ExecuteNonQuery();
                }
                return true;
            }
            catch (System.Exception exp)
            {
                MFEExceptions mfeExp = new MFEExceptions(MessageConstant.MFE_FILE_IMPORT_FAILED , exp);

                throw mfeExp;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="strFilePath"></param>
        /// <returns></returns>
        public bool Import(String strFilePath)
        {
            try
            {
                List<SirenFieldInfo> lstImportFile = this.ParseFile(strFilePath);

                SqlDatabase objDatabase = new SqlDatabase(this.ConnectionString);

                foreach (SirenFieldInfo importRecord in lstImportFile)
                {
                    DbCommand dbCmd = objDatabase.GetSqlStringCommand(SP_INSERT_IMPORT_FILE);

                    dbCmd.CommandType = System.Data.CommandType.StoredProcedure;

                    objDatabase.AddInParameter(dbCmd, ID, DbType.String, importRecord.ID);
                    objDatabase.AddInParameter(dbCmd, PARENT_ID, DbType.String, importRecord.ParentID);
                    objDatabase.AddInParameter(dbCmd, CAPTION, DbType.String, importRecord.Caption);
                    objDatabase.AddInParameter(dbCmd, QUERY_TEXT, DbType.String, importRecord.QueryText);
                    objDatabase.AddInParameter(dbCmd, TABLE_NAME, DbType.String, importRecord.TableName);
                    objDatabase.AddInParameter(dbCmd, ROW_IDENTIFIER, DbType.String, importRecord.RowIdentifier);

                    //dbCmd.ExecuteNonQuery();

                    objDatabase.ExecuteNonQuery(dbCmd);
                }
                return true;
            }
            catch (System.Exception exp)
            {
                MFEExceptions mfeExp = new MFEExceptions(MessageConstant.MFE_FILE_IMPORT_FAILED, exp);

                throw mfeExp;
            }
        }

        /// <summary>
        /// Parse the import file.
        /// </summary>
        /// <param name="strFilePath"></param>
        /// <returns></returns>
        public List<SirenFieldInfo> ParseFile(String strFilePath)
        {
            List<SirenFieldInfo> lstFileImport = new List<SirenFieldInfo>();

            try
            {
                StreamReader srFileReader = new StreamReader(strFilePath);

                while (srFileReader.Peek() >= 0)
                {
                    string[] lstColumn = srFileReader.ReadLine().ToUpper().Split(new Char[] { COLUMN_SEPERATOR });

                    string tableName = DBNull.Value.ToString();

                    string rowIdentifier = DBNull.Value.ToString();

                    if (lstColumn[COLUMN_QUERY_TEXT] != "")
                    {
                        tableName = lstColumn[COLUMN_QUERY_TEXT].Substring(lstColumn[COLUMN_QUERY_TEXT].IndexOf(FROM) + 4).ToString();

                        tableName = tableName.Substring(0, tableName.LastIndexOf(WHERE)).ToString();

                        if (lstColumn[COLUMN_QUERY_TEXT].IndexOf(WHERE) > 0)
                        {
                            rowIdentifier = lstColumn[COLUMN_QUERY_TEXT].Substring(lstColumn[COLUMN_QUERY_TEXT].IndexOf(WHERE) + 5).ToString();
                        }
                        else
                            rowIdentifier = DBNull.Value.ToString();

                        //lstColumn[COLUMN_QUERY_TEXT] = lstColumn[COLUMN_QUERY_TEXT].Substring(0, lstColumn[COLUMN_QUERY_TEXT].IndexOf(FROM) + 4).ToString();
                    }

                    SirenFieldInfo importRecord = new SirenFieldInfo
                    {
                        ID = lstColumn[COLUMN_ID],
                        ParentID = lstColumn[COLUMN_PARENT_ID].Trim(),
                        Caption = lstColumn[COLUMN_CAPTION].Trim(),
                        QueryText = lstColumn[COLUMN_QUERY_TEXT].Trim(),
                        TableName = tableName.Trim(),
                        RowIdentifier = rowIdentifier.Trim(),
                        Visible = true
                    }; 

                    lstFileImport.Add(importRecord);
                }

                srFileReader.Close();

                return lstFileImport;
            }
            catch (System.IO.FileNotFoundException fileExp)
            {
                MFEExceptions mfeFileExp = new MFEExceptions("", fileExp);

                throw mfeFileExp;
            }
            catch (System.Exception exp)
            {
                MFEExceptions mfeFileExp = new MFEExceptions("", exp);

                throw mfeFileExp;
            }
        }
    }
}